package com.treasure.mall.biz.repository.integral;

import com.treasure.mall.biz.base.JpaSearchableRepository;
import com.treasure.mall.biz.entity.integral.UserIntegralLogPO;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.time.LocalDateTime;

/**
 * @author allan
 * @version 1.0.0
 * @date 2020/8/24
 */
@Repository
public interface UserIntegralLogRepository extends JpaSearchableRepository<UserIntegralLogPO, Long> {
    Page<UserIntegralLogPO> findByUserId(Long userId, Pageable pageable);

    @Query(value = "select ifnull(sum(log.integral),0) from zb_user_integral_log log where log.integral>0 and log.is_success=true and (log.create_time between ?1 and ?2)", nativeQuery = true)
    Long totalSending(LocalDateTime begin, LocalDateTime end);

    @Query(value = "select ifnull(sum(log.integral),0) from zb_user_integral_log log where log.integral<0 and log.is_success=true and (log.create_time between ?1 and ?2)", nativeQuery = true)
    Long totalConsume(LocalDateTime begin, LocalDateTime end);

    @Query(value = "select ifnull(count(1),0) from (select distinct log.rel_user_id from zb_user_integral_log log " +
            "where log.integral>0 and log.is_success=true and (log.create_time between ?1 and ?2) " +
            "group by log.rel_user_id) a", nativeQuery = true)
    Long sendUserCount(LocalDateTime begin, LocalDateTime end);


    @Query(value = "select ifnull(count(1),0) from (select distinct log.rel_user_id from zb_user_integral_log log " +
            "where log.integral<0 and log.is_success=true and (log.create_time between ?1 and ?2) " +
            "group by log.rel_user_id) a", nativeQuery = true)
    Long consumeUserCount(LocalDateTime begin, LocalDateTime end);
}
